const db = require('../config/db'); // 导入数据库配置
const { pool } = require('../config/db'); // 引入连接池

class PetComment {
  /**
   * 根据 pet_id 获取评论列表
   * @param {number} petId 宠物ID
   * @returns {Promise<Array>} 评论对象数组
   */
  static async getByPetId(petId) {
    try {
      const connection = await pool.getConnection();
      const [rows] = await connection.execute(
        'SELECT * FROM pet_comments WHERE pet_id = ? ORDER BY created_at DESC',
        [petId]
      );
      connection.release();
      return rows;
    } catch (error) {
      console.error(`获取宠物(ID=${petId})的评论出错:`, error);
      throw error;
    }
  }

  /**
   * 获取最近的评论列表
   * @param {number} limit 每页显示数量
   * @param {number} page 当前页码
   * @param {string} search 搜索关键词
   * @returns {Promise<Array>} 评论对象数组
   */
  static async getRecentComments(limit = 10, page = 1, search = '') {
    try {
      const connection = await pool.getConnection();
      const offset = (page - 1) * limit;
      
      // 构建搜索条件
      const searchCondition = search
        ? `WHERE pc.comment LIKE '%${search}%' OR u.username LIKE '%${search}%' OR p.name LIKE '%${search}%'`
        : '';
      
      // 查询评论列表
      const [rows] = await connection.execute(
        `SELECT pc.*, u.username, p.name as pet_name 
         FROM pet_comments pc 
         JOIN users u ON pc.user_id = u.id 
         JOIN pets p ON pc.pet_id = p.id 
         ${searchCondition}
         ORDER BY pc.created_at DESC 
         LIMIT ${offset}, ${limit}`
      );
      
      connection.release();
      return rows;
    } catch (error) {
      console.error('获取最近评论出错:', error);
      throw error;
    }
  }

  /**
   * 新增评论
   */
  static async create({ pet_id, user_id, rating, comment }) {
    const connection = await pool.getConnection();
    await connection.execute(
      'INSERT INTO pet_comments (pet_id, user_id, comment, rating, created_at) VALUES (?, ?, ?, ?, NOW())',
      [pet_id, user_id, comment, rating]
    );
    connection.release();
  }

  /**
   * 获取某宠物的评论（带用户名）
   */
  static async getByPetIdWithUser(petId) {
    const connection = await pool.getConnection();
    const [rows] = await connection.execute(
      `SELECT pc.*, u.username 
       FROM pet_comments pc 
       JOIN users u ON pc.user_id = u.id 
       WHERE pc.pet_id = ? 
       ORDER BY pc.created_at DESC`,
      [petId]
    );
    connection.release();
    return rows;
  }

  // 这里可以根据需要添加其他方法，例如：
  // static async delete(commentId) { ... }
}

module.exports = PetComment; 